package com.lt.report.service.Impl;


import com.lt.report.bean.HandInput;
import com.lt.report.bean.ItemExpend;
import com.lt.report.bean.WjPmsBean;
import com.lt.report.bean.YearBalance;
import com.lt.report.dao.HandInputMapper;
import com.lt.report.dao.ItemExpendMapper;
import com.lt.report.dao.WjPmsBeanMapper;
import com.lt.report.dao.YearBalanceMapper;
import com.lt.report.service.AsyncService;
import com.lt.report.service.ImportServiceInterFace;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.InputStream;
import java.util.*;

/**
 * @author: yz
 * @Date:
 */
@Service
@Slf4j
public class ImportServiceImpl implements ImportServiceInterFace {
    @Autowired
    ItemExpendMapper itemExpendMapper;
    @Autowired
    WjPmsBeanMapper wjPmsBeanMapper;

    @Autowired
    AsyncService asyncService;

    @Autowired
    YearBalanceMapper yearBalanceMapper;

    @Autowired
    HandInputMapper handInputMapper;

    /**
     * 处理上传的文件
     *
     * @param in
     * @param fileName
     * @return
     * @throws Exception
     */
    public String getBankListByExcel(InputStream in, String fileName) throws Exception {
        //选择文件中的列
        Integer[] arr = null;
        String tableName = "";
        if (fileName.contains("各阶段明细信息项目清单")) {
            arr = new Integer[]{2, 3, 4, 5, 7, 8, 9, 27, 65};
            tableName = "wjPms";
        } else if (fileName.contains("项目支出")) {
            arr = new Integer[]{2, 3, 4, 5, 28};
            tableName = "itemExtend";
        } else if (fileName.contains("HFM工程管理报表_工程管理月报账面数本年累计")) {
            arr = new Integer[]{15, 16, 18, 20};
            tableName = "sumBalance";
        }  else if (fileName.contains("手工填写")) {
            tableName = "handInput";
        }else {
            log.error("上传文件不符合规定");
            return "请上次相关文件";
        }
        List<Integer> cellList = new ArrayList<>();
        if (arr !=null){
            cellList   = Arrays.asList(arr);
        }
        List list = null;
        //创建Excel工作薄
        Workbook work = this.getWorkbook(in, fileName);
        if (null == work) {
            throw new Exception("创建Excel工作薄为空！");
        }
        Sheet sheet = null;
        Row row = null;
        Cell cell = null;
//        for (int i = 0; i < work.getNumberOfSheets(); i++) {
//        for (int i = 0; i < 1; i++) {
        if (tableName.equals("sumBalance")) {
            //项目合计
            sheet = work.getSheet("工程报表");
        } else {
            sheet = work.getSheetAt(0);//只取第一个Sheet
        }
//            if (sheet == null) {
//                continue;
//            }
//        }
        work = null;
        if (tableName.equals("wjPms")) {
            list = this.listStringPMS(sheet, cellList);
            wjPmsBeanMapper.insertAll(list);
        } else if (tableName.equals("itemExtend")) {
            List<List<ItemExpend>> listall = this.listStringItemExtend(sheet, cellList);
            int size = listall.size();
            for (int n = 0; n < size; n++) {
                asyncService.executeAsync(listall.get(n));
            }
        } else if (tableName.equals("sumBalance")) {
            yearBalanceMapper.insertAll(this.listStringSumBalance(sheet,cellList,fileName));
        }else if(tableName.equals("handInput")){
            handInputMapper.insertAll(this.listHandInput(sheet));
        }else{
            return "请上次相关文件";
        }
        return "上传成功";
    }


    /**
     * 导入PMS数据
     *
     * @param sheet
     * @param cellList
     * @return
     */
    public List<WjPmsBean> listStringPMS(Sheet sheet, List<Integer> cellList) {
        List<WjPmsBean> list = new ArrayList<>();
        Cell cell = null;
        for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {
            WjPmsBean wjPmsBean = new WjPmsBean();
            Row row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j || row.getCell(1) == null) {
                continue;
            }
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                if (cellList.contains(y + 1)) {
                    cell = row.getCell(y);
                    //2, 3, 4, 5, 7, 8, 9, 27, 65
                    if (y + 1 == 2) {
                        wjPmsBean.setPrefectrueCity(cell.toString());
                    } else if (y + 1 == 3) {
                        wjPmsBean.setItemCode(cell.toString());
                    } else if (y + 1 == 4) {
                        wjPmsBean.setItemName(cell.toString());
                    } else if (y + 1 == 5) {
                        wjPmsBean.setBuildYear(cell.toString());
                    } else if (y + 1 == 7) {
                        wjPmsBean.setaMajor(cell.toString());
                    } else if (y + 1 == 8) {
                        wjPmsBean.setbMajor(cell.toString());
                    } else if (y + 1 == 9) {
                        wjPmsBean.setcMajor(cell.toString());
                    } else if (y + 1 == 27) {
                        wjPmsBean.setItemLevel(cell.toString());
                    } else if (y + 1 == 65) {
                        cell = row.getCell(y);
                        wjPmsBean.setLevelMoney(cell.toString());
                    }
                }
            }
            list.add(wjPmsBean);
        }
        return list;
    }

    /**
     * 导入手工填写数据
     *
     * @param sheet
     * @return
     */
    public List<HandInput> listHandInput(Sheet sheet) {
        List<HandInput> list = new ArrayList<>();
        Cell cell = null;
        for (int j = sheet.getFirstRowNum() + 1; j <= sheet.getLastRowNum(); j++) {
            Row row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j || row.getCell(1) == null) {
                continue;
            }
            HandInput handInput = new HandInput();
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                cell = row.getCell(y);
               switch (y){
                   case 0: handInput.setItemCity(cell.toString()); break;
                   case 1: handInput.setYearBudget(cell.toString()); break;
                   case 2: handInput.setHouse5g(cell.toString()); break;
                   case 3: handInput.setBase5g(cell.toString()); break;
                   case 4: handInput.setRidInvest(cell.toString()); break;
                   case 5: handInput.setfPowerUp(String.valueOf(Double.valueOf(cell.toString())*100)+'%'); break;
                   case 6: handInput.setCapexBudget(cell.toString()); break;
                   case 7: handInput.setNewItemCost(cell.toString()); break;
               }

            }
            list.add(handInput);
        }
        return list;
    }

    /**
     * 插入项目成本
     *
     * @param sheet
     * @param cellList
     * @return
     */
    public List<List<ItemExpend>> listStringItemExtend(Sheet sheet, List<Integer> cellList) {
        List<List<ItemExpend>> listAll = new ArrayList<>();
        Cell cell = null;
        List<ItemExpend> list = new ArrayList<>();
        for (int j = sheet.getFirstRowNum() + 3; j <= sheet.getLastRowNum(); j++) {
            if (j % 5000 == 0 || j == sheet.getLastRowNum()) {//
                List<ItemExpend> listCopy = new ArrayList<>();
                for (int n = 0; n < list.size(); n++) {
                    listCopy.add(list.get(n));
                }
                listAll.add(listCopy);
                list.clear();
            }
            ItemExpend itemExpend = new ItemExpend();
            Row row = sheet.getRow(j);
            if (row == null || row.getFirstCellNum() == j || row.getCell(1) == null) {
                continue;
            }
            for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
                if (cellList.contains(y + 1)) {
                    cell = row.getCell(y);
                    //2, 3, 4, 5, 28
                    if (y + 1 == 2) {
                        itemExpend.setItemCompany(cell.toString());
                    } else if (y + 1 == 3) {
                        itemExpend.setTaskCompany(cell.toString());
                    } else if (y + 1 == 4) {
                        itemExpend.setItemCode(cell.toString());
                    } else if (y + 1 == 5) {
                        itemExpend.setItemName(cell.toString());
                    } else if (y + 1 == 28) {
                        itemExpend.setItemCost(cell.toString());
                    }
                }
            }
            list.add(itemExpend);
        }
        return listAll;
    }

    /**
     * n年度余额
     *
     * @param sheet
     * @param cellList
     * @return
     */
    public List<YearBalance> listStringSumBalance(Sheet sheet, List<Integer> cellList,String tableName) {
        String name = tableName.substring(0,6);
        Map<String,String> city = new HashMap<>();
        city.put("121501","内蒙古本部");
        city.put("121502","呼和浩特");
        city.put("121503","包头");
        city.put("121504","乌海");
        city.put("121505","赤峰");
        city.put("121506","通辽");
        city.put("121507","鄂尔多斯");
        city.put("121508","呼伦贝尔");
        city.put("121509","巴彦淖尔");
        city.put("121510","乌兰察布");
        city.put("121523","兴安盟");
        city.put("121526","锡林郭勒");
        city.put("121530","阿拉善");
        List<List<YearBalance>> listAll = new ArrayList<>();
        Cell cell = null;
        List<YearBalance> list = new ArrayList<>();
            YearBalance yearBalance = new YearBalance();
            Row row = sheet.getRow(9);
             cell = row.getCell(14);
            Cell  cell15 =row.getCell(15);
            Cell  cell17 =row.getCell(17);
            Cell  cell19 =row.getCell(19);
            yearBalance.setItemCity(city.get(name));
            yearBalance.setBeginBalance(NumberToTextConverter.toText(cell.getNumericCellValue()));
            yearBalance.setItemBalance(NumberToTextConverter.toText(cell19.getNumericCellValue()));
            yearBalance.setpAllBalance(NumberToTextConverter.toText(cell17.getNumericCellValue()));
            yearBalance.setrAllBalance(NumberToTextConverter.toText(cell15.getNumericCellValue()));

            list.add(yearBalance);
//        }
        return list;
    }

    /**
     * 判断文件格式
     *
     * @param inStr
     * @param fileName
     * @return
     * @throws Exception
     */
    private Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
        Workbook workbook = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));
        if (".xls".equals(fileType)) {
            workbook = new HSSFWorkbook(inStr);
        } else if (".xlsx".equals(fileType)) {
            workbook = new XSSFWorkbook(inStr);
        } else {
            throw new Exception("请上传excel文件！");
        }
        inStr.close();
        return workbook;
    }

}
